package control;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import admin.Manager;
import user.Userjdbc;

public class Middlejdbc {
	public ArrayList<Manager> queryMiddle() throws Exception {
		String sql="select * from manager";
		PreparedStatement pre=Userjdbc.getCon(sql);
		ResultSet res=pre.executeQuery();
		ArrayList<Manager> list=new ArrayList<Manager>();
		while(res.next()){
			String mgid=res.getString(1);
			String mgname=res.getString(2);
			String psw=res.getString(3);
			Manager qu=new Manager(mgid,mgname,psw);
			list.add(qu);
		}
		return list;
	}
	
	
	public ArrayList<Column> queryColu() throws Exception {
		String sql="select cid,cname from colu";
		PreparedStatement pre=Userjdbc.getCon(sql);
		ResultSet res=pre.executeQuery();
		ArrayList<Column> list=new ArrayList<Column>();
		while(res.next()){
			String id=res.getString(1);
			String cname=res.getString(2);
			Column c=new Column(id,cname);
			list.add(c);
		}
		return list;
	}
	
	
//	public ArrayList<Middle> queryCid(String mgid) throws Exception {
//		String sql="select cid from middle where mgid=?";
//		PreparedStatement pre=Userjdbc.getCon(sql);
//		pre.setString(1, mgid);
//		ResultSet res=pre.executeQuery();
//		ArrayList<Middle> list=new ArrayList<Middle>();
//		while(res.next()){
//			String cid=res.getString(1);
//			Middle m=new Middle(cid);
//			list.add(m);
//		}
//		return list;
//	}
	
	
	public ArrayList<Column> queryColumn(String mgid) throws Exception {
		String sql="select * from colu where cid in(select cid from middle where mgid=?) ";
		PreparedStatement pre=Userjdbc.getCon(sql);
		pre.setString(1, mgid);
		ResultSet res=pre.executeQuery();
		ArrayList<Column> list=new ArrayList<Column>();
		while(res.next()){
			String id=res.getString(1);
			String cname=res.getString(2);
			String curl=res.getString(3);
			Column c=new Column(id,cname,curl);
			list.add(c);
		}
		return list;
	}
	
	
	

	public void saveMiddle(String mgid,String cid) throws Exception{
		String sql="insert into middle values(?,?,?)";
		PreparedStatement pre=Userjdbc.getCon(sql);
		pre.setString(1, mgid);
		pre.setString(2, cid);
		pre.setString(3, mgid+cid);
		pre.execute();
	}
}
